import re
import json
from copy import deepcopy

import scrapy
from dateutil.parser import parse


def is_dict_or_scrapyItem(data):
    return isinstance(data, (dict, scrapy.Item))


def is_list_or_tuple(data):
    return isinstance(data, (list, tuple))


def is_int(data):
    try:
        int(data)
        return True
    except:
        return False


def is_date(data):
    if re.search(r'\d+-\d+-\d+', data):
        return True
    return False


def is_datetime(data):
    if re.search(r'\d+-\d+-\d+.*?\d{2}:\d{2}:\d{2}', data):
        return True
    return False


def get_sql(fields, table_name, typ):
    if typ == 'insert':
        sql = 'insert into {name}({keys}) values ({values})'.format(name=table_name, keys=','.join(fields), values=','.join(['%s']*len(fields)))
    else:
        sql = 'replace into {name}({keys}) values ({values})'.format(name=table_name, keys=','.join(fields), values=','.join(['%s']*len(fields)))
    return sql


def handle_items(items):
    '''
    return:  处理好的字典的values列表
    '''
    values = list(items.values())
    for index, value in enumerate(values):
        if is_dict_or_scrapyItem(value):
            values[index] = json.dumps(value, ensure_ascii=False)
            continue
        if is_list_or_tuple(value):
            for i, v in enumerate(value):
                if isinstance(v, dict):
                    values[index][i] = json.dumps(v, ensure_ascii=False)
            values[index] = ','.join([str(v) for v in value])
    return values


def get_keys_index(items):
    len_list = [len(item.keys()) for item in items]
    index = len_list.index(max(len_list))
    return index


def handle_values(keys, items):
    if len(items) > 1:
        # 进行字段补全
        b_keys = set(keys)
        for item in items:
            current_item_keys = set(list(item.keys()))
            diff_set = b_keys - current_item_keys
            if diff_set:
                for field in diff_set:
                    item[field] = None
    new_values = []
    o = {}
    for item in items:
        item_keys = item.keys()
        values = handle_items(item)
        v = {k: v for k, v in zip(item_keys, values)}
        new_v = deepcopy(o)
        for key in keys:
            new_v[key] = v[key]
        new_values.append(list(new_v.values()))
    return new_values


def get_current_all_keys(items):
    keys_list = []
    for item in items:
        for key in list(item.keys()):
            keys_list.append(key)
    return set(keys_list)


def get_create_table_keys(items):
    if len(items) == 1:
        return list(items[0].keys())
    else:
        return list(get_current_all_keys(items))


def get_create_table_values(all_keys, items):
    if len(items) == 1:
        return handle_items(items[0])
    else:
        new_dict = {}
        for key in all_keys:                        # 获取到所有的打乱后的key的list
            for item in items:
                if item.get(key, None):
                    new_dict[key] = item.get(key)   # 打乱后的key对应的value
        for key in all_keys:                        # 这里不能使用else，因为如果原来一个字段有值，但是最后一个字段
            if not new_dict.get(key, None):         # 的这个值为None，那么会将原来的值覆盖。在类型判断时会判断为varchar(255)
                new_dict[key] = '无'
        n_dict = {}
        for k in all_keys:                          # 拼接成对应的
            n_dict[k] = new_dict[k]
        return handle_items(n_dict)


def get_typ(value):
    '''
    输入的item必须是已经处理过的item
    '''
    if value is None:
        return 'varchar(255)'
    if isinstance(value, str):
        if is_int(value):
            if int(value) > 2147483647:
                return 'bigint'
            else:
                return 'integer'
        if is_datetime(value):
            return 'datetime'
        if is_date(value):
            return 'date'
        if len(value) < 255:
            return 'varchar(255)'
        elif len(value) < 1024:
            return 'varchar(1024)'
        else:
            return 'text'
    if isinstance(value, int):
        if value > 2147483647:
            return 'bigint'
        else:
            return 'integer'
    if isinstance(value, float):
        return 'double'


def get_create_table_sql(table_name, fields, values):
    fields_list = []
    fields_list.append('id bigint(11) NOT NULL AUTO_INCREMENT')
    for k, v in zip(fields, values):
        typ = get_typ(v)
        fields_list.append('{} {}'.format(k, typ))
    fields_list.append('PRIMARY KEY (id)')
    create_table_sql = 'create table {} ({})'.format(table_name, ','.join(fields_list))
    return create_table_sql


def get_table_all_keys(table_name, cursor):
    result = set()
    sql = "select column_name from information_schema.columns where table_name='{}' and table_schema='{}'".format(table_name, cursor.connection.db.decode())
    cursor.execute(sql)
    for field in cursor.fetchall():
        if is_dict_or_scrapyItem(field):
            result.add(field.get('column_name'))
        else:
            result.add(field[0])
    return result


def get_add_fields_sql(new_dict, table_name):
    fields_list = []
    # 获取类型时，需要对values先做处理
    new_dict = {key: value for key, value in zip(new_dict, handle_items(new_dict))}
    for k, v in new_dict.items():
        typ = get_typ(v)
        fields_list.append('{} {}'.format(k, typ))
    return 'alter table {} add ({})'.format(table_name, ','.join(fields_list))


def get_create_new_fields_sql(table_name, items, cursor):
    '''
    传入的items，获取到所有的key后，可能这个key为空，那么下面就等于没有创建
    '''
    new_fields_dict = {}
    all_current_keys = get_current_all_keys(items)
    all_table_keys = get_table_all_keys(table_name, cursor)
    new_fields_set = all_current_keys - all_table_keys
    if new_fields_set:
        for field in new_fields_set:
            for item in items:
                if item.get(field, None):
                    new_fields_dict[field] = item.get(field)
        for field in new_fields_set:
            if not new_fields_dict.get(field, None):
                new_fields_dict[field] = '无'
        return get_add_fields_sql(new_fields_dict, table_name)
    else:
        return None


def get_add_unique_sql(name, fields):
    if isinstance(fields, str):
        return 'alter table {} add unique index({})'.format(name, fields)
    if isinstance(fields, list):
        return 'alter table {} add unique index({})'.format(name, ','.join(fields))


def get_update_fields_dict_helper(current_table_fields_info, current_item_fields_info):
    update_fields_dict = {}
    for info in current_table_fields_info:
        for s_info in current_item_fields_info:
            if info['column_name'] == s_info['column_name']:
                if info['column_type'] != s_info['column_type']:
                    if s_info['column_type'] == "integer":
                        pass
                    elif s_info['column_type'] == "varchar(255)":
                        pass
                    else:
                        update_fields_dict[s_info['column_name']] = s_info['column_type']
    return update_fields_dict


def get_update_fields_length_dict(table_name, items, cursor):
    #  integer -> bigint      varchar(255) -> varchar(1024) -> text
    sql = "select column_name, column_type from information_schema.columns where table_name='{}' and table_schema='{}'".format(table_name, cursor.connection.db.decode())
    cursor.execute(sql)
    current_table_fields_info = cursor.fetchall()
    if isinstance(current_table_fields_info, tuple):
        current_table_fields_info = [{"column_name": info[0], "column_type": info[1]} for info in current_table_fields_info]
    if len(items) == 1:
        # 这里需要先对传入的item进行处理，否则item中如有字典或列表类型，在get_type中没有这类类型的处理，就会返回None
        new_item = {key: value for key, value in zip(items[0], handle_items(items[0]))}
        current_item_fields_info = [{"column_name": k, "column_type": get_typ(v)} for k, v in new_item.items()]
        update_fields_dict = get_update_fields_dict_helper(current_table_fields_info, current_item_fields_info)
        return update_fields_dict
    else:
        update_fields_dict = {}
        for item in items:
            new_item = {key: value for key, value in zip(item, handle_items(item))}
            one_item_fields_info = [{"column_name": k, "column_type": get_typ(v)} for k, v in new_item.items()]
            fields_dict = get_update_fields_dict_helper(current_table_fields_info, one_item_fields_info)
            for k, v in fields_dict.items():
                if k not in list(update_fields_dict.keys()):
                    update_fields_dict[k] = v
        return update_fields_dict


def get_update_fields_length_sql(table_name, items, cursor):
    fields_dict = get_update_fields_length_dict(table_name, items, cursor)
    for k, v in fields_dict.items():
        if v == "varchar(1024)":
            fields_dict[k] = "text"

    for k, v in fields_dict.items():
        sql = "alter table {} modify {} {}".format(table_name, k, v)
        yield sql


def id_exists_helper(keys):
    for key in keys:
        if key.lower() == 'id':
            raise KeyError('id字段不允许出现在KEY中')


def id_exists(items):
    for item in items:
        id_exists_helper(list(item.keys()))


def judge_type(data):
    for item in data:
        if not is_dict_or_scrapyItem(item):
            print(type(item))
            raise TypeError('传入的items必须是字典，列表，元组或scrapy.Item()的对象')


def commit(db):
    if db:
        db.commit()


def rollback(db):
    if db:
        db.rollback()
        raise
    raise


def error_back(exception, item):
    print("异步执行SQL语句出错，错误信息：{}，错误数据：{}".format(exception, item))
